import pandas as pd
import os

class DataStorage:
    def __init__(self, file_path="data.xlsx"):
        self.file_path = file_path
        self.users_file = f"{file_path}#users"
        self.inventory_file = f"{file_path}#inventory"
        self.tasks_file = f"{file_path}#tasks"
        self.initialize_data_file()

    def initialize_data_file(self):
        if not os.path.exists(self.file_path):
            with pd.ExcelWriter(self.file_path, mode='w') as writer:
                pd.DataFrame(columns=['id', 'username', 'password_hash', 'role']).to_excel(writer, sheet_name='users', index=False)
                pd.DataFrame(columns=['商品编号', '商品名称', '商品单位', '零售价', '商品规格', '扩展信息']).to_excel(writer, sheet_name='inventory', index=False)
                pd.DataFrame(columns=['id', 'title', 'description', 'status', 'due_date', 'assigned_to']).to_excel(writer, sheet_name='tasks', index=False)

    def read_users(self):
        try:
            return pd.read_excel(self.file_path, sheet_name='users')
        except Exception as e:
            print(f"Error reading users: {e}")
            return pd.DataFrame(columns=['id', 'username', 'password_hash', 'role'])

    def write_users(self, df):
        with pd.ExcelWriter(self.file_path, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name='users', index=False)

    def read_inventory(self):
        try:
            return pd.read_excel(self.file_path, sheet_name='inventory')
        except Exception as e:
            print(f"Error reading inventory: {e}")
            return pd.DataFrame(columns=['商品编号', '商品名称', '商品单位', '零售价', '商品规格', '扩展信息'])

    def write_inventory(self, df):
        with pd.ExcelWriter(self.file_path, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name='inventory', index=False)

    def read_tasks(self):
        try:
            return pd.read_excel(self.file_path, sheet_name='tasks')
        except Exception as e:
            print(f"Error reading tasks: {e}")
            return pd.DataFrame(columns=['id', 'title', 'description', 'status', 'due_date', 'assigned_to'])

    def write_tasks(self, df):
        with pd.ExcelWriter(self.file_path, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name='tasks', index=False)